CREATE VIEW [dbo].[vPledgeData]
   AS
    SELECT  
        Activity.ID                                ID,
        'Pledge' AS                                TransactionType,
        MAX(Pledge.TRANS_NUMBER)                   TransactionNumber,
        MAX(Pledge.INVOICE_REFERENCE_NUM)          InvoiceReferenceNumber,
        MAX(Pledge.TRANSACTION_DATE)               TransactionDate,
        MAX(Activity.EFFECTIVE_DATE)               DateReceived,
        CONVERT(int,substring(CONVERT(char(6),MAX(Pledge.FISCAL_PERIOD)),1,4)) AS FiscalYear,
        CONVERT(int,substring(CONVERT(char(6),MAX(Pledge.FISCAL_PERIOD)),5,2)) AS FiscalMonth,
        MAX(Activity.SOURCE_CODE)                  Appeal,
        MAX(Activity.CAMPAIGN_CODE)                Campaign,
        MAX(Activity.ORG_CODE)                     Fund,
        MAX(Activity.SOLICITOR_ID)                 SolicitorID,
        MAX(Invoice.SOURCE_SYSTEM)                 SourceSystem,
        MAX(Pledge.MATCH_GIFT_TRANS_NUM)           MatchingTransaction,
        MAX(Pledge.IS_MATCH_GIFT)                  IsMatchingGift,
        MAX(Pledge.MEM_TRIB_ID)                    MemorialID,
        MAX(Activity.ACTION_CODES)                 ListAs,
        MAX(Activity.UF_4)                         RequestNumber,
        MAX(Invoice.INSTALL_BILL_DATE)             InstallmentDate,
        NULL AS                                    LastPaymentDate,
        SUM(Pledge.AMOUNT) * -1 AS                 PledgeAmount,
        0 AS                                       AdjustmentsAmount,
        0 AS                                       PaymentsAmount,
        SUM(Pledge.TAXABLE_VALUE)                  PledgeFairMarketValue,
        MAX(Pledge.MEM_TRIB_NAME_TEXT)             MemorialNameText     
    FROM Trans Pledge
        INNER JOIN (SELECT DISTINCT TransactionNumber FROM TransWatch) tw ON
            tw.TransactionNumber = Pledge.TRANS_NUMBER
        INNER JOIN Invoice ON
            Pledge.INVOICE_REFERENCE_NUM = Invoice.REFERENCE_NUM
        INNER JOIN Activity ON
            Pledge.ACTIVITY_SEQN = Activity.SEQN
    WHERE
        Pledge.TRANSACTION_TYPE = 'DIST' AND
        Pledge.JOURNAL_TYPE = 'IN' AND
        Pledge.POSTED >= 2 AND
        Invoice.ORIGINATING_TRANS_NUM = Pledge.TRANS_NUMBER AND
        Invoice.SOURCE_SYSTEM IN  ('FR', 'DUES') AND
        Activity.ACTIVITY_TYPE = 'PLEDGE'
    GROUP BY Activity.ID, Pledge.INVOICE_REFERENCE_NUM, Activity.ORG_CODE,Activity.CAMPAIGN_CODE,Activity.SOURCE_CODE
UNION ALL  
    SELECT     
        MAX(P.ST_ID) AS ID,
        'Asterisk Tab Adjustment' AS TransactionType,
        0 AS TransactionNumber,
        MAX(C.INVOICE_REFERENCE_NUM)   AS InvoiceReferenceNumber,
        NULL AS asTransactionDate,
        NULL AS DateReceived,
        0 AS FiscalYear,
        0 AS FiscalMonth,
        MAX(P.SOURCE_CODE)  AS Appeal,
        MAX(P.CAMPAIGN_CODE) AS Campaign,
        MAX(C.OWNER_ORG_CODE) AS Fund,
        '' AS SolicitorID,
        MAX(dbo.Invoice.SOURCE_SYSTEM)  AS SourceSystem,
        0 AS MatchingTransaction,
        0 AS IsMatchingGift,
        '' AS MemorialID,
        '' AS ListAs,
        0 AS RequestNumber,
        NULL AS InstallmentDate,
        NULL AS LastPaymentDate,
        0 AS PledgeAmount,
        0 AS AdjustmentsAmount,
        SUM(C.AMOUNT) * - 1 AS PaymentsAmount,
        0 AS PledgeFairMarketValue,
        '' AS MemorialNameText
    FROM  dbo.Trans AS C
        INNER JOIN  dbo.Invoice ON
            dbo.Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
        INNER JOIN  dbo.Trans AS P ON
            P.TRANS_NUMBER = dbo.Invoice.ORIGINATING_TRANS_NUM
        LEFT OUTER JOIN dbo.Cash_Accounts ON  
            dbo.Cash_Accounts.CASH_ACCOUNT_CODE = C.CHECK_NUMBER
        INNER JOIN dbo.Activity ON
            dbo.Activity.SEQN = P.ACTIVITY_SEQN
        INNER JOIN (SELECT DISTINCT TransactionNumber FROM TransWatch) tw ON
            tw.TransactionNumber = P.TRANS_NUMBER
    WHERE     
        (P.LINE_NUMBER = 1) AND (P.SUB_LINE_NUMBER = 1) AND
        (P.JOURNAL_TYPE = 'IN') AND (P.TRANSACTION_TYPE = 'DIST') AND
        (dbo.Invoice.SOURCE_SYSTEM = 'FR') AND (C.JOURNAL_TYPE = 'PAY') AND
        (C.TRANSACTION_TYPE = 'PAY') OR
        (P.LINE_NUMBER = 1) AND (P.SUB_LINE_NUMBER = 1) AND
        (P.JOURNAL_TYPE = 'IN') AND (P.TRANSACTION_TYPE = 'DIST') AND
        (dbo.Invoice.SOURCE_SYSTEM = 'FR') AND (C.JOURNAL_TYPE = 'IN') AND
        (C.TRANSACTION_TYPE = 'TR')
    GROUP BY P.ST_ID, P.TRANS_NUMBER, P.INVOICE_REFERENCE_NUM
UNION ALL
    SELECT  
        CASE WHEN Invoice_Lines.ST_ID IS NULL THEN Invoice.BT_ID ELSE Invoice_Lines.ST_ID END    ID,
        'Adjustment' AS                    TransactionType,
        0 AS                            Transaction_Number,
        MAX(Adj.INVOICE_REFERENCE_NUM)    InvoiceReferenceNumber,
        NULL AS                            TransactionDate,
        NULL AS                            DateReceived,
        0 AS                            FiscalYear,
        0 AS                            FiscalMonth,
        MAX(P.SOURCE_CODE)  AS            Appeal,
        MAX(P.CAMPAIGN_CODE)            Campaign,
        MAX(Adj.OWNER_ORG_CODE)            Fund,
        ''  AS                            SolicitorID,
        MAX(Invoice.SOURCE_SYSTEM)        SourceSystem,
        0  AS                            MatchingTransaction,
        0  AS                            IsMatchingGift,
        ''   AS                            MemorialID,
        ''   AS                            ListAs,
        0  AS                            RequestNumber,
        NULL AS                            InstallmentDate,
        NULL AS                            LastPaymentDate,
        0  AS                            PledgeAmount,
        SUM(Adj.AMOUNT) * -1  AS        AdjustmentsAmount,
        0  AS                            PaymentsAmount,
        0 AS                            PledgeFairMarketValue,
        ''   AS                            MemorialNameText   
    FROM Trans Adj
        INNER JOIN (SELECT DISTINCT InvoiceNumber FROM TransWatch) tw ON
            tw.InvoiceNumber = Adj.INVOICE_REFERENCE_NUM
        INNER JOIN Invoice ON
            Adj.INVOICE_REFERENCE_NUM = Invoice.REFERENCE_NUM
        INNER JOIN Trans P ON
            P.TRANS_NUMBER = Invoice.ORIGINATING_TRANS_NUM
        INNER JOIN Activity ON
            Activity.SEQN = P.ACTIVITY_SEQN
        LEFT OUTER JOIN Invoice_Lines ON
            Invoice_Lines.REFERENCE_NUM = Adj.INVOICE_REFERENCE_NUM AND
            Invoice_Lines.LINE_NUM = Adj.INVOICE_LINE_NUM
    WHERE  
        ((Adj.TRANSACTION_TYPE = 'DIST' and Adj.JOURNAL_TYPE = 'CM') OR
        (Adj.TRANSACTION_TYPE = 'DIST' and Adj.JOURNAL_TYPE = 'DM')) and
        Adj.POSTED >= 2 and
        ((Invoice.SOURCE_SYSTEM = 'FR' and Invoice.INSTALL_BILL_DATE is not NULL and
        P.SUB_LINE_NUMBER = 1 AND Activity.ACTIVITY_TYPE='PLEDGE' and P.LINE_NUMBER = 1) or (Invoice.SOURCE_SYSTEM='DUES' and P.PRODUCT_CODE = Adj.PRODUCT_CODE and P.INVOICE_LINE_NUM = Adj.INVOICE_LINE_NUM))
    GROUP BY Invoice.BT_ID, Invoice_Lines.ST_ID, Adj.INVOICE_REFERENCE_NUM, Adj.OWNER_ORG_CODE,P.CAMPAIGN_CODE, P.SOURCE_CODE
UNION ALL
    SELECT  
        CASE WHEN Invoice_Lines.ST_ID IS NULL THEN Invoice.BT_ID ELSE Invoice_Lines.ST_ID END    ID,
        'Payment' AS                                TransactionType,
        0  AS                                       Transaction_Number,
        MAX(Pay.INVOICE_REFERENCE_NUM)              InvoiceReferenceNumber,
        NULL AS                                     TransactionDate,
        NULL AS                                     DateReceived,
        0  AS                                       FiscalYear,
        0  AS                                       FiscaMonth,
        P.SOURCE_CODE  AS                                      Appeal,
        P.CAMPAIGN_CODE  AS                                      Campaign,
        (case when MAX(Pay.TRANSACTION_TYPE) = 'AR'
                 then MAX(Pay.GL_ACCT_ORG_CODE)
                 else MAX(Pay.OWNER_ORG_CODE) end) Fund,
        ''  AS                                      SolicitorID,
        MAX(Invoice.SOURCE_SYSTEM)                  SourceSystem,
        0  AS                                       MatchingTransaction,
        0  AS                                       IsMatchingGift,
        ''   AS                                     MemorialID,
        ''   AS                                     ListAs,
        0  AS                                       RequestNumber,
        NULL AS                                     InstallmentDate,
        MAX(Pay.TRANSACTION_DATE)                   LastPaymentDate,
        0  AS                                       PledgeAmount,
        0  AS                                       AdjustmentsAmount,
        SUM(Pay.INVOICE_CREDITS)                    PaymentsAmount,
        0 AS                                        PledgeFairMarketValue,
        ''   AS                                     MemorialNameText    
    FROM Trans Pay
        INNER JOIN (SELECT DISTINCT InvoiceNumber FROM TransWatch) tw ON
            tw.InvoiceNumber = Pay.INVOICE_REFERENCE_NUM
        INNER JOIN Invoice ON
            Invoice.REFERENCE_NUM = Pay.INVOICE_REFERENCE_NUM
        INNER JOIN Trans P ON
            P.TRANS_NUMBER = Invoice.ORIGINATING_TRANS_NUM
        INNER JOIN Activity ON
            Activity.SEQN = P.ACTIVITY_SEQN
        LEFT OUTER JOIN Invoice_Lines ON
            Invoice_Lines.REFERENCE_NUM = Pay.INVOICE_REFERENCE_NUM AND Invoice_Lines.LINE_NUM = Pay.INVOICE_LINE_NUM
    WHERE  
        P.TRANSACTION_TYPE = 'DIST' and Pay.POSTED >= 2 and Pay.IS_FR_ITEM=1 AND
        Activity.ACTIVITY_TYPE='PLEDGE' AND
        (
         (
          P.LINE_NUMBER = 1  and P.SUB_LINE_NUMBER = 1 and  Invoice.SOURCE_SYSTEM='FR' and
           (
            (Pay.JOURNAL_TYPE = 'PAY' and Pay.TRANSACTION_TYPE = 'AR')
            or
            (Pay.JOURNAL_TYPE = 'IN' and Pay.TRANSACTION_TYPE = 'PAY')
           )
          )
          or
          (
           Invoice.SOURCE_SYSTEM='DUES' and P.PRODUCT_CODE = Pay.PRODUCT_CODE and Pay.JOURNAL_TYPE = 'PAY' and Pay.TRANSACTION_TYPE = 'AR' and P.INVOICE_LINE_NUM = Pay.INVOICE_LINE_NUM
          )
         )
         GROUP BY Invoice.BT_ID, Invoice_Lines.ST_ID, Pay.INVOICE_REFERENCE_NUM, Pay.OWNER_ORG_CODE,Pay.GL_ACCT_ORG_CODE, P.CAMPAIGN_CODE, P.SOURCE_CODE
UNION  
    SELECT ID, TransactionType, PledgeReport.TransactionNumber, InvoiceReferenceNumber, TransactionDate, DateReceived, FiscalYear, FiscalMonth, AppealCode, CampaignCode, Fund, SolicitorID, SourceSystem, MatchingTransaction, IsMatchingGift, MemorialID, ListAs, RequestNumber, InstallmentDate, LastPaymentDate, PledgeAmount, AdjustmentsAmount, PaymentsAmount, PledgeFairMarketValue, MemorialNameText
    FROM PledgeReport
    WHERE
        InvoiceReferenceNumber NOT IN (SELECT DISTINCT InvoiceNumber FROM TransWatch)

GO
GRANT REFERENCES ON  [dbo].[vPledgeData] TO [IMIS]
GRANT SELECT ON  [dbo].[vPledgeData] TO [IMIS]
GRANT INSERT ON  [dbo].[vPledgeData] TO [IMIS]
GRANT DELETE ON  [dbo].[vPledgeData] TO [IMIS]
GRANT UPDATE ON  [dbo].[vPledgeData] TO [IMIS]
GO